package Database;
import Model.Product;


import Model.SalesOrder;

import java.sql.*;
import java.util.ArrayList;


public class DBProduct implements IFDBProduct{
    private  Connection connection;
	private String name;
    /** Creates a new instance of DBProduct */
    public DBProduct() {
      connection = DbConnection.getInstance().getDBcon();
    }
    
  //Implements the methods from the interface
    // get all products
    public ArrayList<Product> getAllEmployees(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }
    //get one product by having the product name
    public Product findProduct(int id, boolean retrieveAssociation)
    {   String wClause = "  productID = " + id + "";
        return singleWhere(wClause, retrieveAssociation);
    }
    //find one product by having the product name
    public String searchProductName(String name, boolean retriveAssociation)
    {
        String wClause = "name like '%" + name + "%'";
        System.out.println("SearchProduct " + wClause);
        return null;//singleWhere(wClause, retriveAssociation);
    }
      
     //insert a new product
    @Override
    public int insertProduct(Product product) throws Exception
    {  //call to get the next product id
   
       int rc = -1;
	   String query="INSERT INTO product(productID, pName, purchasePrice, salesPrice, countryOfOrigin, minStock, supplierID)  VALUES("+
	  		product.getProductID()  + ",'"  +
		    product.getName()  + "',"  +
		    product.getPurchasePrice()  + ","  +
		    product.getSalesPrice()  + ",'"  +
		    product.getCountryOfOrigin()  + "',"  +
		    product.getMinStock()  + ","  +
            product.getSupplierID() + ")";
                     

       System.out.println("insert : " + query);
      try{ // insert new product +  dependent
          Statement stmt = connection.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("Product is not created");
          throw new Exception ("Product is not inserted correctly");
       }
       return(rc);
    }
@Override
	public int updateProduct(Product product)
	{
		Product productObj  = product;
		int rc=-1;

		String query="UPDATE product SET "+
		 	   "productID ="+ productObj.getProductID()+", "+
		 	   "pName ='"+ productObj.getName() + "', " +
               "purchasePrice ="+ productObj.getPurchasePrice() + ", " +
               "salesPrice ="+ productObj.getSalesPrice() + ", " +
               "countryOfOrigin ='"+ productObj.getCountryOfOrigin() + "', " +
               "minStock ="+ productObj.getMinStock() + ", " +
               "supplierID ="+ productObj.getSupplierID() + " " +
		          " WHERE productID = "+ productObj.getProductID() + "";
                System.out.println("Update query:" + query);
  		try{ // update product
	 		Statement stmt = connection.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}// try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in product database: " + ex);
	  	}
		return(rc);
	}
	
	public int delete(int productID)
	{
               int rc=-1;
	  
	  	String query="DELETE FROM product WHERE productID = '" +
				productID + "'";
                System.out.println(query);
	  	try{ // delete from product
	 		Statement stmt = connection.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}// try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in product database: " + ex);
   	        }
		return(rc);
	}
	
	//private methods
    //michWere is used whenever we want to select more than one product
	
	 
	private ArrayList<Product> miscWhere(String name, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<Product> list = new ArrayList<Product>();	
		
	    String query =  buildQuery(name);
  
            try{ // read the employee from the database
		Statement stmt = connection.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
	     	 Product productObj;
		 productObj = buildProduct1(results);	
                 list.add(productObj);	
		}//end while
                 stmt.close();     
                 if(retrieveAssociation)
                 {   
                     for(Product productObj : list){
                        String superproductName = productObj.getName();
                       // String superProductName = singleWhere(" name = '" + superproductName + "'",false);
                        Object superProduct;
						
                        System.out.println("Product is seleceted");
                       // 
                 }//end if   
			
		}
            }// try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	

	private Product buildProduct1(ResultSet results) {
	   Product pro = new Product();
		try { // the columns from the table emplayee are used
		    pro.setProductID(results.getInt("productID"));
		    pro.setName(results.getString("pName"));
		    
		    pro.setPurchasePrice(results.getDouble("purchasePrice"));
		    pro.setSalesPrice(results.getDouble("salesPrice"));
		    pro.setCountryOfOrigin(results.getString("countryOfOrigin"));
		    pro.setMinStock(results.getInt("minStock"));
		    pro.setSupplierID(results.getInt("supplierID"));
		} catch (Exception e) {
		    System.out.println("error in building the product object");
		}
		return pro;
	    }


	//SingleWhere is used when we only select one products
	private Product singleWhere(String wClause, boolean retrieveAssociation)
	{
	   
		ResultSet results;
		Product productObj = null;
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{ // read the product from the database
	 		Statement stmt = connection.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            productObj = buildProduct1(results);
                            //Association is to be build
                            stmt.close();
                            if(retrieveAssociation)
                            {   
                                String superproduct = productObj.getName();
                               // String superProductName = singleWhere(" product name = '" + name + "'",false);
                                
                                System.out.println("Product is selected");
                               // here the department has to be selected as well
                           
                           
                            }
			}
                        else{ //no product was found
                           
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: " + e);
	 	}
		return productObj;
	}
	//method to build the query
	private String buildQuery(String string)
	{
	    String query="SELECT productID, pName, purchasePrice, salesPrice, countryOfOrigin, minStock, supplierID FROM product";
		
		
		
			query=query + " WHERE " + string;
			
		return query;
	}

	public void deleteProduct(int productID) {
		
		
	}

	public Product findProduct(int productID) {
	
		return null;
	}

	
	public ArrayList<Product> getAllProducts(boolean retriveAssociation) {
	   
	    return null;
	}

	
	public Product searchProductID1(int productID, boolean b) {
	   
	    return null;
	}


	

	
	}
 
